package zy.dao.shop.gift.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.shop.gift.GiftDAO;
import zy.entity.base.product.T_Base_Product;
import zy.entity.base.size.T_Base_Size;
import zy.entity.shop.gift.T_Shop_Gift;
import zy.entity.shop.gift.T_Shop_GiftList;
import zy.entity.shop.gift.T_Shop_Gift_Product;
import zy.entity.shop.gift.T_Shop_Gift_Run;
import zy.entity.shop.gift.T_Shop_Gift_Send;
import zy.entity.sys.user.T_Sys_User;
import zy.util.CommonUtil;
import zy.util.DateUtil;
import zy.util.StringUtil;
@Repository
public class GiftDAOImpl extends BaseDaoImpl implements GiftDAO{

	@Override
	public Integer count(Map<String, Object> param) {
		Object pd_no = param.get("pd_no");
		Object begindate = param.get("begindate");
		Object enddate = param.get("enddate");
		Object gi_state = param.get("gi_state");
		Object gi_shop_code = param.get("gi_shop_code");
		
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_shop_gift t");
		sql.append(" join t_base_product pd on pd.pd_code = t.gi_pd_code and pd.companyid = t.companyid ");
		sql.append(" join t_base_shop sp on sp.sp_code = t.gi_shop_code AND sp.companyid = t.companyid ");
		sql.append(" where 1 = 1");
		if(begindate!=null && !begindate.equals("")){
			sql.append(" AND t.gi_sysdate>='"+begindate+" 00:00:00 '");
		}
		if(enddate!=null && !enddate.equals("")){
			sql.append(" AND t.gi_sysdate<='"+enddate +" 23:59:59 '");
		}
		
        if(null != pd_no && !"".equals(pd_no)){
			sql.append(" AND pd.pd_no=:pd_no");
		}
		if(null != gi_state && !"".equals(gi_state)){
			sql.append(" AND t.gi_state=:gi_state");
		}
		
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			if(null != gi_shop_code && !"".equals(gi_shop_code)){
				sql.append(" AND t.gi_shop_code=:gi_shop_code");
			}else {
				sql.append(" AND sp.sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.THREE+" OR sp_shop_type = "+CommonUtil.FIVE+")");
			}
		}else if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FOUR.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、加盟店、合伙店
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" and t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Shop_Gift> list(Map<String, Object> param) {
		Object start = param.get("start");
		Object end = param.get("end");
		Object pd_no = param.get("pd_no");
		Object begindate = param.get("begindate");
		Object enddate = param.get("enddate");
		Object gi_state = param.get("gi_state");
		Object gi_shop_code = param.get("gi_shop_code");
		
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
        
		StringBuffer sql = new StringBuffer("");
		sql.append(" select t.gi_id,t.gi_pd_code,t.gi_shop_code,t.gi_begindate,t.gi_enddate,t.gi_sysdate,t.gi_state,t.gi_point,");
		sql.append(" pd.pd_no,pd.pd_name,sp.sp_name,");
		sql.append(" (select sum(gil_totalamount) from t_shop_giftlist gil where gil.gil_pd_code = t.gi_pd_code and gil.gil_shop_code = t.gi_shop_code and gil.companyid = t.companyid ) as totalamount,");
		sql.append(" (select sum(gil_getamount) from t_shop_giftlist gil where gil.gil_pd_code = t.gi_pd_code and gil.gil_shop_code = t.gi_shop_code and gil.companyid = t.companyid) as getamount");
		sql.append(" from t_shop_gift t");
		sql.append(" join t_base_product pd on pd.pd_code = t.gi_pd_code and pd.companyid = t.companyid ");
		sql.append(" join t_base_shop sp on sp.sp_code = t.gi_shop_code AND sp.companyid = t.companyid ");
		sql.append(" where 1 = 1");
		if(begindate!=null && !begindate.equals("")){
			sql.append(" AND t.gi_sysdate>='"+begindate+" 00:00:00 '");
		}
		if(enddate!=null && !enddate.equals("")){
			sql.append(" AND t.gi_sysdate<='"+enddate +" 23:59:59 '");
		}
		
        if(null != pd_no && !"".equals(pd_no)){
			sql.append(" AND pd.pd_no=:pd_no");
		}
		if(null != gi_state && !"".equals(gi_state)){
			sql.append(" AND t.gi_state=:gi_state");
		}
		
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			if(null != gi_shop_code && !"".equals(gi_shop_code)){
				sql.append(" AND t.gi_shop_code=:gi_shop_code");
			}else {
				sql.append(" AND sp.sp_upcode = :shop_code AND (sp_shop_type = "+CommonUtil.THREE+" OR sp_shop_type = "+CommonUtil.FIVE+")");
			}
		}else if(CommonUtil.THREE.equals(shop_type) || CommonUtil.FOUR.equals(shop_type) || CommonUtil.FIVE.equals(shop_type)){//自营店、加盟店、合伙店
			sql.append(" AND sp.sp_code = :shop_code");
		}
		sql.append(" and t.companyid=:companyid");
		sql.append(" order by t.gi_id desc ");
		if(null != start && !"".equals(start) && null != end && !"".equals(end)){
			sql.append(" limit :start,:end");
		}
		List<T_Shop_Gift> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Shop_Gift.class));
		return list;
	}

	@Override
	public void deleteTemp(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_shop_giftlist_temp WHERE gil_user_code = :us_code AND companyid = :companyid ");
		namedParameterJdbcTemplate.update(sql.toString(), param);
	}

	@Override
	public List<String> queryDpCodeByShop(Map<String, Object> param) {
		Object companyid = param.get("companyid");
		Object gil_shop_code = param.get("gil_shop_code");
		StringBuffer sql = new StringBuffer("");
		sql.append(" select dp_code from t_base_depot t where companyid = "+companyid+" and dp_shop_code = '"+gil_shop_code+"'");
		List<String> list  = namedParameterJdbcTemplate.getJdbcOperations().queryForList(sql.toString(),String.class);
		return list;
	}

	@Override
	public Integer count_product(Map<String, Object> param) {
		Object searchContent = param.get("searchContent");
		String dp_code = param.get("dp_code").toString();
		Object exactQuery = param.get("exactQuery");
		
		if(!StringUtil.trimString(dp_code).equals("")){
			dp_code = "'"+dp_code.replace(",", "','")+"'";
		}
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT count(1)");
		sql.append(" FROM (");
		sql.append(" SELECT 1,IFNULL((SELECT SUM(sd_amount) FROM t_stock_data sd WHERE sd_dp_code IN("+dp_code+") AND sd.sd_pd_code = t.pd_code AND sd.companyid = t.companyid),0) AS sd_amount ");
		sql.append(" FROM t_base_product t");
		sql.append(" LEFT JOIN t_shop_gift gi ON gi.gi_pd_code = t.pd_code AND gi.companyid = t.companyid AND gi.gi_shop_code = :gil_shop_code");
		sql.append(" where 1 = 1");
		sql.append(" AND gi.gi_id IS NULL ");//未发布礼品
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		sql.append(" AND t.pd_state != '2' ");
		sql.append(" AND t.pd_present = '1' ");
		sql.append(" and t.companyid=:companyid");
		sql.append(" HAVING(sd_amount>0) ");
		sql.append(" )t");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
	}

	@Override
	public List<T_Base_Product> list_product(Map<String, Object> param) {
		Object sidx = param.get(CommonUtil.SIDX);
		Object sord = param.get(CommonUtil.SORD);
		Object searchContent = param.get("searchContent");
		String dp_code = param.get("dp_code").toString();
		Object exactQuery = param.get("exactQuery");
		
		if(!StringUtil.trimString(dp_code).equals("")){
			dp_code = "'"+dp_code.replace(",", "','")+"'";
		}
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT pd_id,pd_code,pd_no,pd_name,pd_unit,pd_szg_code,pd_bra,pd_sell_price,pd_score,pd_year,pd_season,");
		sql.append(" IFNULL((SELECT SUM(sd_amount) FROM t_stock_data sd WHERE sd_dp_code IN("+dp_code+") AND sd.sd_pd_code = t.pd_code AND sd.companyid = t.companyid),0) AS sd_amount,");
		sql.append(" (SELECT count(1) FROM t_base_product_img pdm WHERE pdm.pdm_pd_code = t.pd_code AND pdm.companyid = t.companyid) AS img_count ");
		sql.append(" FROM t_base_product t");
		sql.append(" LEFT JOIN t_shop_gift gi ON gi.gi_pd_code = t.pd_code AND gi.companyid = t.companyid AND gi.gi_shop_code = :gil_shop_code");
		sql.append(" where 1 = 1");
		sql.append(" AND gi.gi_id IS NULL ");//未发布礼品
		if(StringUtil.isNotEmpty(searchContent)){
			if("1".equals(exactQuery)){
				sql.append(" AND (t.pd_name = :searchContent OR t.pd_no = :searchContent)");
			}else {
				sql.append(" AND (instr(t.pd_name,:searchContent)>0 OR instr(t.pd_spell,:searchContent)>0 OR instr(t.pd_no,:searchContent)>0)");
			}
        }
		sql.append(" AND t.pd_state != '2' ");
		sql.append(" AND t.pd_present = '1' ");
		sql.append(" and t.companyid=:companyid");
		sql.append(" HAVING(sd_amount>0) ");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY pd_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Product.class));
	}

	@Override
	public Map<String, Object> temp_loadproduct(Map<String, Object> param) {
		Map<String,Object> resultMap= new HashMap<String, Object>();
		T_Sys_User user = (T_Sys_User)param.get("user");
		String sizeGroupCode="";
		String dp_code = (String)param.get("dp_code");
		
		StringBuffer sql = new StringBuffer("");
		sql.append("select pd_szg_code,pd_unit,pd_no,pd_code,pd_name,pd_season,pd_year,pd_cost_price,");
		sql.append("(select bd_name from t_base_brand bd where bd.bd_code=t.pd_bd_code and bd.companyid=t.companyid LIMIT 1) as pd_bd_name,");//品牌
		sql.append("(select tp_name from t_base_type tp where tp.tp_code=t.pd_tp_code and tp.companyid=t.companyid LIMIT 1) as pd_tp_name,");//类别
		sql.append(" IFNULL(pdp_sell_price,pd_sell_price) AS pd_sell_price, ");
		sql.append(" IFNULL(pdp_sort_price,pd_sort_price) AS pd_sort_price, ");
		sql.append(" (SELECT pdm_img_path FROM t_base_product_img pdm WHERE pdm.pdm_pd_code = t.pd_code AND pdm.companyid = t.companyid AND pdm_state = '0' LIMIT 1) AS pdm_img_path");
		sql.append(" from t_base_product t ");
		sql.append(" LEFT JOIN t_base_product_shop_price pdp ON pdp.companyid = t.companyid AND pdp.pdp_pd_code = t.pd_code AND pdp_shop_code = :gil_shop_code ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND t.pd_code = :pd_code ");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" limit 1");
		T_Base_Product product = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Product.class)).get(0);
		resultMap.put("product",product);
		
		//获取尺码组
		sizeGroupCode = product.getPd_szg_code();
		//获取尺码信息
		sql.setLength(0);
		sql.append("select sz_id,sz_code,sz_name");
		sql.append(" from t_base_sizelist t");
		sql.append(" join t_base_size sz on sz.sz_code=t.szl_sz_code and sz.companyid=t.companyid");
		sql.append(" where 1=1");
		sql.append(" and t.szl_szg_code = '"+sizeGroupCode+"'");
		sql.append(" and t.companyid = :companyid");
		sql.append(" ORDER BY szl_id ");
		List<T_Base_Size> sizeList = new ArrayList<T_Base_Size>();
		sizeList = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Size.class));
		resultMap.put("sizes",sizeList);
			
		//获取颜色杯型信息
		sql.setLength(0);
		sql.append(" select DISTINCT cr_code,cr_name,ifnull(br_code,'') as br_code,ifnull(br_name,'') as br_name ");
		sql.append(" from t_base_product_color pdc ");
		sql.append(" left join t_base_color cr on cr.cr_code=pdc.pdc_cr_code and cr.companyid=:companyid");
		sql.append(" left join t_base_product_br pdb on pdb.pdb_pd_code=:pd_code and pdb.companyid=:companyid");
		sql.append(" left join t_base_bra br on br.br_code= pdb.pdb_br_code and br.companyid=:companyid");
		sql.append(" where 1=1 ");
		sql.append(" and pdc.pdc_pd_code=:pd_code");
		sql.append(" and pdc.companyid=:companyid");
		sql.append(" order by cr_name,br_name");
		List<T_Shop_Gift_Product> inputs = new ArrayList<T_Shop_Gift_Product>();
		inputs = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Shop_Gift_Product.class));
		resultMap.put("braColors",inputs);
		
		//已录入数量
		sql.setLength(0);
		sql.append(" SELECT gil_sz_code as sz_code,gil_bs_code as br_code,gil_cr_code as cr_code,");
		sql.append(" gil_totalamount as totalamount,gil_getamount as getamount,gil_addamount as addamount ");
		sql.append(" FROM t_shop_giftlist_temp ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND gil_pd_code = :pd_code ");
		sql.append(" AND gil_user_code = "+user.getUs_code());
		sql.append(" AND companyid = :companyid");
		List<T_Shop_Gift_Product> listTempInput= new ArrayList<T_Shop_Gift_Product>();
		listTempInput = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Shop_Gift_Product.class));
		resultMap.put("amountInputs",listTempInput);
		
		//备份库存
		sql.setLength(0);
		sql.append(" SELECT sd_sz_code as sz_code,sd_br_code as br_code,sd_cr_code as cr_code,SUM(sd_amount) AS totalamount ");
		sql.append(" FROM t_stock_data t ");
		sql.append(" INNER JOIN t_base_depot dp ON dp.dp_code = t.sd_dp_code AND dp.companyid = t.companyid ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND sd_pd_code = :pd_code ");
		if(!StringUtil.trimString(dp_code).equals("")){
			dp_code = dp_code.replace(",", "','");
			sql.append(" AND sd_dp_code IN ('"+dp_code+"')");
		}
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY t.sd_sz_code,t.sd_br_code,t.sd_cr_code");
		List<T_Shop_Gift_Product> listTempBackup= new ArrayList<T_Shop_Gift_Product>();
		listTempBackup = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Shop_Gift_Product.class));
		resultMap.put("amountStocks",listTempBackup);
		return resultMap;
	}

	@Override
	public List<T_Shop_GiftList> temp_list(Map<String, Object> param) {
		Object sidx = param.get(CommonUtil.SIDX);
		Object sord = param.get(CommonUtil.SORD);
		String dp_code = (String)param.get("dp_code");
		if(!StringUtil.trimString(dp_code).equals("")){
			dp_code = "'"+dp_code.replace(",", "','")+"'";
		}
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT gil_id,gil_pd_code,gil_cr_code,gil_sz_code,gil_szg_code,gil_bs_code,gil_totalamount,gil_getamount,gil_sysdate,");
		sql.append(" gil_shop_code,gil_user_code,gil_addamount,t.companyid,pd_name,pd_no,pd_code,pd_unit,pd_score,");
		sql.append(" IFNULL((SELECT SUM(sd_amount) FROM t_stock_data sd "); 
		sql.append(" where sd.sd_code = gil_sub_code and sd_dp_code IN("+dp_code+") AND sd.companyid = t.companyid),0) as sd_amount,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = gil_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = gil_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = gil_bs_code AND br.companyid = t.companyid LIMIT 1) AS br_name");
		sql.append(" FROM t_shop_giftlist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.gil_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1=1");
		sql.append(" AND gil_user_code = :gil_user_code");
		sql.append(" AND t.companyid = :companyid");
		if(StringUtil.isNotEmpty(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY gil_id DESC");
		}
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Shop_GiftList.class));
	}

	@Override
	public List<T_Shop_GiftList> temp_sum(String us_code, Integer companyid) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT gil_id,gil_pd_code,pd_code,pd_name,pd_no,pd_score,SUM(gil_totalamount) as gil_totalamount,SUM(gil_totalamount-gil_getamount) as gil_lastamount,");
		sql.append(" gil_user_code,gil_sysdate,t.companyid,pd_unit,gil_shop_code,gil_point ");
		sql.append(" FROM t_shop_giftlist_temp t");
		sql.append(" JOIN t_base_product pd ON pd_code = t.gil_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1 = 1 ");
		sql.append(" AND gil_user_code = :us_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY gil_pd_code ");
		return namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("us_code", us_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Shop_GiftList.class));
	}

	@Override
	public List<String> temp_szgcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT DISTINCT gil_szg_code");
		sql.append(" FROM t_shop_giftlist_temp t ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND gil_user_code = :gil_user_code");
		sql.append(" AND t.companyid = :companyid");
		return namedParameterJdbcTemplate.queryForList(sql.toString(), params, String.class);
	}

	@Override
	public void temp_save(Map<String, Object> params) {
		List<T_Shop_GiftList> listTemps=(List<T_Shop_GiftList>)params.get("temps");
		T_Sys_User user = (T_Sys_User)params.get("user");
		String pd_code = (String)params.get("pd_code");
		if(listTemps==null){
			listTemps = new ArrayList<T_Shop_GiftList>();
		}
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT IFNULL(gil_point,pd_score) AS gil_point ");
		sql.append(" FROM t_base_product t");
		sql.append(" LEFT JOIN t_shop_giftlist_temp gil ON gil.gil_pd_code = t.pd_code AND gil.companyid = t.companyid AND gil.gil_user_code = :us_code ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND t.pd_code = :pd_code ");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" LIMIT 1 ");
		Integer gil_point = 0;
		gil_point = namedParameterJdbcTemplate.queryForObject(sql.toString(),
				new MapSqlParameterSource().addValue("us_code", user.getUs_code())
				.addValue("companyid", user.getCompanyid()).addValue("pd_code", pd_code) ,Integer.class);
		
		List<T_Shop_GiftList> temps_add = new ArrayList<T_Shop_GiftList>();
		List<T_Shop_GiftList> temps_update = new ArrayList<T_Shop_GiftList>();
		List<T_Shop_GiftList> temps_del = new ArrayList<T_Shop_GiftList>();
		for (T_Shop_GiftList item : listTemps) {
			item.setGil_point(gil_point);
			item.setGil_getamount(0);
			item.setGil_sysdate(DateUtil.getCurrentTime());
			item.setGil_shop_code(user.getUs_shop_code());
			item.setGil_user_code(user.getUs_code());
			item.setCompanyid(user.getCompanyid());
			if ("add".equals(item.getOperateType())){
				if (item.getGil_totalamount() > 0) {
					temps_add.add(item);
				}
			}else{
				if(!item.getGil_totalamount().equals(0)){
					temps_update.add(item);
				}else {
					temps_del.add(item);
				}
			}
		}
		
		//增加
		if (temps_add.size() > 0) {
			sql.setLength(0);
			sql.append(" INSERT INTO t_shop_giftlist_temp "); 
			sql.append(" (gil_sub_code,gil_pd_code,gil_cr_code,gil_sz_code,gil_szg_code,gil_bs_code,gil_totalamount,gil_getamount,gil_sysdate, ");
			sql.append(" gil_shop_code,gil_user_code,companyid,gil_addamount,gil_point) ");
			sql.append(" VALUES ");
			sql.append("(:gil_sub_code,:gil_pd_code,:gil_cr_code,:gil_sz_code,:gil_szg_code,:gil_bs_code,:gil_totalamount,:gil_getamount,:gil_sysdate, ");
			sql.append(" :gil_shop_code,:gil_user_code,:companyid,:gil_addamount,:gil_point)");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps_add.toArray()));
		}
		//修改
		if (temps_update.size() > 0) {
			sql.setLength(0);
			sql.append(" UPDATE t_shop_giftlist_temp ");
			sql.append(" SET gil_totalamount = :gil_totalamount");
			sql.append(" WHERE ");
			sql.append(" gil_sub_code = :gil_sub_code ");
			sql.append(" AND gil_user_code = :gil_user_code");
			sql.append(" AND companyid = :companyid");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps_update.toArray()));
		}
		//修改
		if (temps_del.size() > 0) {
			sql.setLength(0);
			sql.append(" DELETE FROM t_shop_giftlist_temp ");
			sql.append(" WHERE ");
			sql.append(" gil_sub_code = :gil_sub_code ");
			sql.append(" AND gil_user_code = :gil_user_code");
			sql.append(" AND companyid = :companyid");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps_del.toArray()));
		}
	}

	@Override
	public void temp_save_modify(Map<String, Object> params) {
		List<T_Shop_GiftList> listTemps=(List<T_Shop_GiftList>)params.get("temps");
		T_Sys_User user = (T_Sys_User)params.get("user");
		String pd_code = (String)params.get("pd_code");
		if(listTemps==null){
			listTemps = new ArrayList<T_Shop_GiftList>();
		}
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT IFNULL(gil_point,pd_score) AS gil_point ");
		sql.append(" FROM t_base_product t");
		sql.append(" LEFT JOIN t_shop_giftlist_temp gil ON gil.gil_pd_code = t.pd_code AND gil.companyid = t.companyid AND gil.gil_user_code = :us_code ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND t.pd_code = :pd_code ");
		sql.append(" AND t.companyid = :companyid ");
		sql.append(" LIMIT 1 ");
		Integer gil_point = 0;
		gil_point = namedParameterJdbcTemplate.queryForObject(sql.toString(),
				new MapSqlParameterSource().addValue("us_code", user.getUs_code())
				.addValue("companyid", user.getCompanyid()).addValue("pd_code", pd_code) ,Integer.class);
		
		List<T_Shop_GiftList> temps_add = new ArrayList<T_Shop_GiftList>();
		List<T_Shop_GiftList> temps_update = new ArrayList<T_Shop_GiftList>();
		List<T_Shop_GiftList> temps_del = new ArrayList<T_Shop_GiftList>();
		for (T_Shop_GiftList item : listTemps) {
			item.setGil_point(gil_point);
			item.setGil_getamount(0);
			item.setGil_sysdate(DateUtil.getCurrentTime());
			item.setGil_shop_code(user.getUs_shop_code());
			item.setGil_user_code(user.getUs_code());
			item.setCompanyid(user.getCompanyid());
			if ("add".equals(item.getOperateType())){
				if (item.getGil_addamount() > 0) {
					temps_add.add(item);
				}
			}else{
				if(!item.getGil_addamount().equals(0) || !item.getGil_totalamount().equals(0)){
					temps_update.add(item);
				}else {
					temps_del.add(item);
				}
			}
		}
		
		//增加
		if (temps_add.size() > 0) {
			sql.setLength(0);
			sql.append(" INSERT INTO t_shop_giftlist_temp "); 
			sql.append(" (gil_sub_code,gil_pd_code,gil_cr_code,gil_sz_code,gil_szg_code,gil_bs_code,gil_totalamount,gil_sysdate, ");
			sql.append(" gil_shop_code,gil_user_code,companyid,gil_addamount,gil_point) ");
			sql.append(" VALUES ");
			sql.append("(:gil_sub_code,:gil_pd_code,:gil_cr_code,:gil_sz_code,:gil_szg_code,:gil_bs_code,0,:gil_sysdate, ");
			sql.append(" :gil_shop_code,:gil_user_code,:companyid,:gil_addamount,:gil_point)");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps_add.toArray()));
		}
		//修改
		if (temps_update.size() > 0) {
			sql.setLength(0);
			sql.append(" UPDATE t_shop_giftlist_temp ");
			sql.append(" SET gil_addamount = :gil_addamount");
			sql.append(" WHERE ");
			sql.append(" gil_sub_code = :gil_sub_code ");
			sql.append(" AND gil_user_code = :gil_user_code");
			sql.append(" AND companyid = :companyid");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps_update.toArray()));
		}
		//删除
		if (temps_del.size() > 0) {
			sql.setLength(0);
			sql.append(" DELETE FROM t_shop_giftlist_temp ");
			sql.append(" WHERE ");
			sql.append(" gil_sub_code = :gil_sub_code ");
			sql.append(" AND gil_user_code = :gil_user_code");
			sql.append(" AND companyid = :companyid");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps_del.toArray()));
		}
	}

	@Override
	public void temp_del(Integer gil_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_shop_giftlist_temp");
		sql.append(" WHERE gil_id=:gil_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("gil_id", gil_id));
	}

	@Override
	public void temp_delByPiCode(T_Shop_GiftList temp) {
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_shop_giftlist_temp");
		sql.append(" WHERE 1=1");
		sql.append(" AND gil_pd_code = :gil_pd_code");
		if(StringUtil.isNotEmpty(temp.getGil_cr_code())){
			sql.append(" AND gil_cr_code = :gil_cr_code");
		}
		if(StringUtil.isNotEmpty(temp.getGil_bs_code())){
			sql.append(" AND gil_bs_code = :gil_bs_code");
		}
		sql.append(" AND gil_user_code = :gil_user_code");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(temp));
	}

	@Override
	public void temp_updateAmountById(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update t_shop_giftlist_temp set ");
		sql.append(" gil_totalamount=:amount");
		sql.append(" where gil_id=:gil_id");
		namedParameterJdbcTemplate.update(sql.toString(), params);
	}

	@Override
	public void temp_updatePointByPdcode(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update t_shop_giftlist_temp set ");
		sql.append(" gil_point=:point");
		sql.append(" where gil_pd_code=:pd_code");
		sql.append(" and gil_user_code=:us_code");
		sql.append(" and companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), params);
	}

	@Override
	public void save(T_Shop_Gift gift, T_Sys_User user) {
		String us_code = user.getUs_code();
		Integer companyid = user.getCompanyid();
		//1.从临时表中查询发布的礼品
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT gil_pd_code,pd_name,pd_score,SUM(gil_totalamount) AS gil_totalamount,gil_point ");
		sql.append(" FROM t_shop_giftlist_temp t ");
		sql.append(" JOIN t_base_product pd ON pd_code = t.gil_pd_code AND pd.companyid = t.companyid ");
		sql.append(" WHERE 1 = 1 ");
		sql.append(" AND gil_user_code = :us_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" GROUP BY gil_pd_code ");
		List<T_Shop_GiftList> temps = namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("us_code", us_code).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Shop_GiftList.class));
		if(temps == null || temps.size() == 0){
			throw new RuntimeException("订单已保存，请勿重复提交");
		}
		
		//2.验证这些礼品是否已经发布
		sql.setLength(0);
		sql.append(" SELECT gi_id FROM t_shop_gift ");
		sql.append(" WHERE 1=1");
		if (temps.size() > 0) {
			sql.append(" AND gi_pd_code IN(");
			for (int i = 0; i < temps.size(); i++) {
				sql.append("'"+temps.get(i).getGil_pd_code()+"'");
				if(i<temps.size()-1){
					sql.append(",");
				}
			}
			sql.append(")");
		}
		sql.append(" AND gi_shop_code = :gi_shop_code");
		sql.append(" AND companyid = :companyid");
		List<T_Shop_Gift> giftList = namedParameterJdbcTemplate.query(sql.toString(),
				new MapSqlParameterSource().addValue("gi_shop_code", gift.getGi_shop_code()).addValue("companyid", companyid),
				new BeanPropertyRowMapper<>(T_Shop_Gift.class));
		if(giftList != null && giftList.size()>0){
			throw new RuntimeException("礼品已经发布!");
		}
		
		//3.保存发布礼品数据
		sql.setLength(0);
		sql.append(" INSERT INTO t_shop_gift");
		sql.append(" (gi_pd_code,gi_shop_code,gi_begindate,gi_enddate,gi_sysdate,gi_state,gi_point,companyid) ");
		sql.append(" VALUES ");
		sql.append(" (:gil_pd_code,'"+gift.getGi_shop_code()
									+"','"+gift.getGi_begindate()
									+"','"+gift.getGi_enddate()
									+"','"+DateUtil.getCurrentTime()
									+"',0,:gil_point,"+companyid+")");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(temps.toArray()));
		
		
		List<T_Shop_GiftList> giftLists = new ArrayList<T_Shop_GiftList>();
		sql.setLength(0);
		sql.append(" SELECT gil_sub_code,gil_pd_code,gil_cr_code,gil_sz_code,gil_szg_code,gil_bs_code,gil_totalamount,gil_sysdate,");
		sql.append(" gil_shop_code,gil_user_code,t.companyid ");
		sql.append(" FROM t_shop_giftlist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND gil_user_code = :us_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY gil_pd_code DESC");
		giftLists = namedParameterJdbcTemplate.query(sql.toString()
				,new MapSqlParameterSource().addValue("us_code", us_code).addValue("companyid", companyid)
				, new BeanPropertyRowMapper<>(T_Shop_GiftList.class));
		
		//4保存子表信息
		sql.setLength(0);
		sql.append(" insert into t_shop_giftlist ");
		sql.append(" (gil_sub_code,gil_pd_code,gil_cr_code,gil_sz_code,gil_szg_code,gil_bs_code,gil_totalamount,gil_getamount, ");
		sql.append(" gil_sysdate,gil_shop_code,gil_user_code,companyid) ");
		sql.append(" VALUES ");
		sql.append(" (:gil_sub_code,:gil_pd_code,:gil_cr_code,:gil_sz_code,:gil_szg_code,:gil_bs_code,:gil_totalamount,0, ");
		sql.append(" '"+DateUtil.getCurrentTime()+"','"+gift.getGi_shop_code()+"',:gil_user_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(giftLists.toArray()));
		
		//5保存流水号信息
		sql.setLength(0);
		sql.append(" insert into t_shop_gift_run ");
		sql.append(" (gir_sub_code,gir_pd_code,gir_cr_code,gir_sz_code,gir_szg_code,gir_bs_code,gir_totalamount, ");
		sql.append(" gir_sysdate,gir_shop_code,gir_user_code,companyid,gir_number,gir_begindate,gir_enddate) ");
		sql.append(" VALUES ");
		sql.append(" (:gil_sub_code,:gil_pd_code,:gil_cr_code,:gil_sz_code,:gil_szg_code,:gil_bs_code,:gil_totalamount, ");
		sql.append(" '"+DateUtil.getCurrentTime()+"','"+gift.getGi_shop_code()+"',:gil_user_code,:companyid,'"
						+DateUtil.getYearMonthDateYYYYMMDD()+"0001','"+gift.getGi_begindate()+"','"+gift.getGi_enddate()+"')");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(giftLists.toArray()));
		
		//6删除临时表
		sql.setLength(0);
		sql.append("DELETE FROM t_shop_giftlist_temp WHERE gil_user_code = :us_code AND companyid = :companyid ");
		namedParameterJdbcTemplate.update(sql.toString(),new MapSqlParameterSource().addValue("us_code", us_code).addValue("companyid", companyid));
	}

	@Override
	public void update(T_Shop_Gift gift, T_Sys_User user) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_shop_gift");
		sql.append(" SET gi_enddate = '"+gift.getGi_enddate()+"', ");
		sql.append(" gi_point = '"+gift.getGi_point()+"' ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND gi_shop_code = :gi_shop_code");
		sql.append(" AND gi_pd_code = :gi_pd_code");
		sql.append(" AND companyid = :companyid");
		namedParameterJdbcTemplate.update(sql.toString(),new MapSqlParameterSource().addValue("gi_shop_code", gift.getGi_shop_code())
				.addValue("gi_pd_code", gift.getGi_pd_code()).addValue("companyid", user.getCompanyid()));
		
		//删除子表数据
		sql.setLength(0);
		sql.append(" DELETE FROM  t_shop_giftlist");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND gil_pd_code = :gi_pd_code ");
		sql.append(" AND gil_shop_code = :gi_shop_code ");
		sql.append(" AND companyid = :companyid ");
		namedParameterJdbcTemplate.update(sql.toString(),new MapSqlParameterSource().addValue("gi_shop_code", gift.getGi_shop_code())
				.addValue("gi_pd_code", gift.getGi_pd_code()).addValue("companyid", user.getCompanyid()));
		
		//查询新的流水号
		sql.setLength(0);
		sql.append(" SELECT CONCAT('"+DateUtil.getYearMonthDateYYYYMMDD()+"',f_addnumber(max(gir_number))) AS gir_number ");
		sql.append(" FROM t_shop_gift_run ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND INSTR(gir_number,'"+DateUtil.getYearMonthDateYYYYMMDD()+"') > 0 ");
		sql.append(" AND gir_shop_code = :gi_shop_code ");
		sql.append(" AND gir_pd_code = :gi_pd_code ");
		sql.append(" AND companyid = :companyid ");
		List<T_Shop_Gift_Run> gift_Run = namedParameterJdbcTemplate.query(sql.toString(), new MapSqlParameterSource().addValue("gi_shop_code", gift.getGi_shop_code())
				.addValue("gi_pd_code", gift.getGi_pd_code()).addValue("companyid", user.getCompanyid()), new BeanPropertyRowMapper<>(T_Shop_Gift_Run.class));
		String gir_number = "";
		if(gift_Run != null && gift_Run.size()>0){
			gir_number = gift_Run.get(0).getGir_number();
		}
		
		//保存子表信息
		List<T_Shop_GiftList> giftLists = new ArrayList<T_Shop_GiftList>();
		sql.setLength(0);
		sql.append(" SELECT gil_sub_code,gil_pd_code,gil_cr_code,gil_sz_code,gil_szg_code,gil_bs_code,gil_totalamount,gil_getamount,gil_addamount,gil_sysdate,");
		sql.append(" gil_shop_code,gil_user_code,t.companyid ");
		sql.append(" FROM t_shop_giftlist_temp t");
		sql.append(" WHERE 1=1");
		sql.append(" AND gil_user_code = :us_code");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" ORDER BY gil_pd_code DESC");
		giftLists = namedParameterJdbcTemplate.query(sql.toString()
				,new MapSqlParameterSource().addValue("us_code", user.getUs_code()).addValue("companyid", user.getCompanyid())
				, new BeanPropertyRowMapper<>(T_Shop_GiftList.class));
		
		sql.setLength(0);
		sql.append(" insert into t_shop_giftlist ");
		sql.append(" (gil_sub_code,gil_pd_code,gil_cr_code,gil_sz_code,gil_szg_code,gil_bs_code,gil_totalamount,gil_getamount, ");
		sql.append(" gil_sysdate,gil_shop_code,gil_user_code,companyid) ");
		sql.append(" VALUES ");
		sql.append(" (:gil_sub_code,:gil_pd_code,:gil_cr_code,:gil_sz_code,:gil_szg_code,:gil_bs_code,:gil_totalamount+:gil_addamount,:gil_getamount, ");
		sql.append(" '"+DateUtil.getCurrentTime()+"','"+gift.getGi_shop_code()+"',:gil_user_code,:companyid)");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(giftLists.toArray()));
		
		//5保存流水号信息
		List<T_Shop_GiftList> runGiftLists = new ArrayList<T_Shop_GiftList>();
		for(int i=0;i<giftLists.size();i++){
			Integer gil_addamount = giftLists.get(i).getGil_addamount();
			if(gil_addamount!=null && gil_addamount>0){
				runGiftLists.add(giftLists.get(i));
			}
		}
		sql.setLength(0);
		sql.append(" insert into t_shop_gift_run ");
		sql.append(" (gir_sub_code,gir_pd_code,gir_cr_code,gir_sz_code,gir_szg_code,gir_bs_code,gir_totalamount, ");
		sql.append(" gir_sysdate,gir_shop_code,gir_user_code,companyid,gir_number,gir_begindate,gir_enddate) ");
		sql.append(" VALUES ");
		sql.append(" (:gil_sub_code,:gil_pd_code,:gil_cr_code,:gil_sz_code,:gil_szg_code,:gil_bs_code,:gil_addamount, ");
		sql.append(" '"+DateUtil.getCurrentTime()+"','"+gift.getGi_shop_code()+"',:gil_user_code,:companyid,'"
						+gir_number+"','"+gift.getGi_begindate()+"','"+gift.getGi_enddate()+"')");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(runGiftLists.toArray()));
		
		//6删除临时表
		sql.setLength(0);
		sql.append("DELETE FROM t_shop_giftlist_temp WHERE gil_user_code = :us_code AND companyid = :companyid ");
		namedParameterJdbcTemplate.update(sql.toString(),new MapSqlParameterSource().addValue("us_code", user.getUs_code()).addValue("companyid", user.getCompanyid()));
	}

	@Override
	public Integer count_run(Map<String, Object> param) {
		Object begindate = param.get("begindate");
		Object enddate = param.get("enddate");
		Object gir_number = param.get("gir_number");
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" select count(1)");
		sql.append(" from t_shop_gift_run t");
		sql.append(" where 1 = 1");
		if(begindate!=null && !begindate.equals("")){
			sql.append(" AND t.gir_sysdate>='"+begindate+" 00:00:00 '");
		}
		if(enddate!=null && !enddate.equals("")){
			sql.append(" AND t.gir_sysdate<='"+enddate +" 23:59:59 '");
		}
		if(!StringUtil.trimString(gir_number).equals("")){
			sql.append(" AND INSTR(gir_number,'"+gir_number+"')>0 ");
		}
		sql.append(" AND gir_pd_code =  :gir_pd_code");
		sql.append(" AND gir_shop_code = :gir_shop_code ");
		sql.append(" AND companyid = :companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), param, Integer.class);
		return count;
	}

	@Override
	public List<T_Shop_Gift_Run> list_run(Map<String, Object> param) {
		Object start = param.get("start");
		Object end = param.get("end");
		Object begindate = param.get("begindate");
		Object enddate = param.get("enddate");
		Object gir_number = param.get("gir_number");
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" select t.gir_id,t.gir_number,t.gir_pd_code,t.gir_totalamount,t.gir_begindate,t.gir_enddate,");
		sql.append(" t.gir_shop_code,pd.pd_no,pd.pd_name,");
		sql.append(" DATE_FORMAT(t.gir_sysdate,'%Y-%m-%d') AS gir_sysdate,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = gir_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = gir_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = gir_bs_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" (SELECT us_name FROM t_sys_user us WHERE us_code = gir_user_code AND us.companyid = t.companyid LIMIT 1) AS user_name ");
		sql.append(" from t_shop_gift_run t");
		sql.append(" join t_base_product pd on pd.pd_code = t.gir_pd_code and pd.companyid = t.companyid ");
		sql.append(" where 1 = 1");
		if(begindate!=null && !begindate.equals("")){
			sql.append(" AND t.gir_sysdate>='"+begindate+" 00:00:00 '");
		}
		if(enddate!=null && !enddate.equals("")){
			sql.append(" AND t.gir_sysdate<='"+enddate +" 23:59:59 '");
		}
		if(!StringUtil.trimString(gir_number).equals("")){
			sql.append(" AND INSTR(gir_number,'"+gir_number+"')>0 ");
		}
		sql.append(" AND gir_pd_code =  :gir_pd_code");
		sql.append(" AND gir_shop_code = :gir_shop_code ");
		sql.append(" AND t.companyid = :companyid");
		sql.append(" order by t.gir_id desc ");
		if(null != start && !"".equals(start) && null != end && !"".equals(end)){
			sql.append(" limit :start,:end");
		}
		List<T_Shop_Gift_Run> list = namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Shop_Gift_Run.class));
		return list;
	}

	@Override
	public void show_gift(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update t_shop_gift set gi_state =:state ");
		sql.append(" where 1=1 ");
		sql.append(" and gi_pd_code =:pd_code and  gi_shop_code=:shop_code and companyid=:companyid");
		namedParameterJdbcTemplate.update(sql.toString(), params);
	}

	@Override
	public void del(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		//删除主表数据
		sql.append(" delete from t_shop_gift ");
		sql.append(" where gi_id= :gi_id");
		namedParameterJdbcTemplate.update(sql.toString(), params);
		
		//删除子表数据
		sql.setLength(0);
		sql.append(" delete from t_shop_giftlist ");
		sql.append(" where 1 = 1 ");				
	    sql.append(" and gil_pd_code = :pd_code");
	    sql.append(" and gil_shop_code= :shop_code");
	    sql.append(" and companyid = :companyid");
	    namedParameterJdbcTemplate.update(sql.toString(), params);
	    
	    //删除流水明细数据
	    sql.setLength(0);
	    sql.append(" DELETE FROM t_shop_gift_run ");
		sql.append(" WHERE 1=1 ");
		sql.append(" AND gir_pd_code = :pd_code ");
		sql.append(" AND gir_shop_code = :shop_code ");
		sql.append(" AND companyid = :companyid ");
		namedParameterJdbcTemplate.update(sql.toString(), params);
	}

	@Override
	public T_Shop_Gift load(Integer gi_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" select t.gi_id,t.gi_pd_code,t.gi_shop_code,t.gi_begindate,t.gi_enddate,t.gi_sysdate,t.gi_state,t.gi_point,");
		sql.append(" pd.pd_no,pd.pd_name,sp.sp_name,sp.sp_upcode,sp.sp_shop_type,");
		sql.append(" (select sum(gil_totalamount) from t_shop_giftlist gil where gil.gil_pd_code = t.gi_pd_code and gil.gil_shop_code = t.gi_shop_code and gil.companyid = t.companyid ) as totalamount,");
		sql.append(" (select sum(gil_getamount) from t_shop_giftlist gil where gil.gil_pd_code = t.gi_pd_code and gil.gil_shop_code = t.gi_shop_code and gil.companyid = t.companyid) as getamount");
		sql.append(" from t_shop_gift t");
		sql.append(" join t_base_product pd on pd.pd_code = t.gi_pd_code and pd.companyid = t.companyid ");
		sql.append(" join t_base_shop sp on sp.sp_code = t.gi_shop_code AND sp.companyid = t.companyid ");
		sql.append(" WHERE gi_id = :gi_id");
		sql.append(" LIMIT 1");
		try {
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("gi_id", gi_id),
					new BeanPropertyRowMapper<>(T_Shop_Gift.class));
		} catch (Exception e) {
			return null;
		}
	}

	@Override
	public void initGiftUpdate(Map<String, Object> params) {
		String us_code = (String)params.get("us_code");
		String gi_shop_code = (String)params.get("gi_shop_code");
		Integer companyid = (Integer)params.get("companyid");
		//删除临时表数据
		StringBuffer sql = new StringBuffer("");
		sql.append("DELETE FROM t_shop_giftlist_temp WHERE gil_user_code = :us_code AND companyid = :companyid ");
		namedParameterJdbcTemplate.update(sql.toString(), params);
		
		//查询修改表数据
		sql.setLength(0);
		sql.append(" SELECT gil_sub_code,gil_pd_code,gil_cr_code,gil_sz_code,gil_szg_code,gil_bs_code,gil_totalamount,");
		sql.append(" gil_getamount,gil_sysdate,gil_shop_code,gil_user_code,t.companyid ");
		sql.append(" FROM t_shop_giftlist t");
		sql.append(" WHERE 1=1");
		sql.append(" AND gil_pd_code = :gi_pd_code");
		sql.append(" AND gil_shop_code = :gi_shop_code");
		sql.append(" AND t.companyid = :companyid");
		List<T_Shop_GiftList> giftLists = namedParameterJdbcTemplate.query(sql.toString(), params,new BeanPropertyRowMapper<>(T_Shop_GiftList.class));
		
		//把添加表数据同步到临时表
		sql.setLength(0);
		sql.append(" INSERT INTO t_shop_giftlist_temp "); 
		sql.append(" (gil_sub_code,gil_pd_code,gil_cr_code,gil_sz_code,gil_szg_code,gil_bs_code,gil_totalamount, ");
		sql.append(" gil_getamount,gil_sysdate,gil_shop_code,gil_user_code,companyid) ");
		sql.append(" VALUES ");
		sql.append(" (:gil_sub_code,:gil_pd_code,:gil_cr_code,:gil_sz_code,:gil_szg_code,:gil_bs_code,:gil_totalamount,");
		sql.append(" :gil_getamount,'"+DateUtil.getCurrentTime()+"','"+gi_shop_code+"','"+us_code+"',"+companyid+")");
		namedParameterJdbcTemplate.batchUpdate(sql.toString(), SqlParameterSourceUtils.createBatch(giftLists.toArray()));
	}

	@Override
	public void updateGiftListTempAddAmount(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" update t_shop_giftlist_temp set ");
		sql.append(" gil_addamount=:amount");
		sql.append(" where gil_id=:gil_id");
		namedParameterJdbcTemplate.update(sql.toString(), params);
	}

	@Override
	public Integer getGiftTempSum(Map<String, Object> params) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT SUM(gil_totalamount+gil_addamount) AS gil_totalamount ");
		sql.append(" FROM t_shop_giftlist_temp ");
		sql.append(" WHERE 1 = 1 ");
		sql.append(" AND gil_user_code = :gil_user_code");
		sql.append(" AND companyid = :companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(),params ,Integer.class);
	}

	@Override
	public Integer sendCount(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT count(1) ");
		sql.append(" FROM t_shop_giftlist t");
		sql.append(" JOIN t_shop_gift g");
		sql.append(" ON g.gi_pd_code=t.gil_pd_code");
		sql.append(" AND g.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND SYSDATE() BETWEEN gi_begindate AND gi_enddate");
		sql.append(" AND g.gi_state=0");//上架
		sql.append(" AND gi_shop_code = :shop_code");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param ,Integer.class);
	}

	@Override
	public List<T_Shop_GiftList> sendList(Map<String, Object> param) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT gil_id,gil_sub_code,gil_pd_code,gil_cr_code,gi_enddate,");
		sql.append(" (SELECT pd_name FROM t_base_product p WHERE p.pd_code=gi_pd_code AND p.companyid=g.companyid LIMIT 1) pd_name,");
		sql.append(" (SELECT cr_name FROM t_base_color p WHERE p.cr_code=gil_cr_code AND p.companyid=t.companyid LIMIT 1) cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size p WHERE p.sz_code=gil_sz_code AND p.companyid=t.companyid LIMIT 1) sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra p WHERE p.br_code=gil_bs_code AND p.companyid=t.companyid LIMIT 1) br_name,");
		sql.append(" gil_sz_code,gil_bs_code,1 gil_amount,gil_totalamount,gil_getamount,gi_point");
		sql.append(" FROM t_shop_giftlist t");
		sql.append(" JOIN t_shop_gift g");
		sql.append(" ON g.gi_pd_code=t.gil_pd_code");
		sql.append(" AND g.companyid=t.companyid");
		sql.append(" WHERE 1=1");
		sql.append(" AND SYSDATE() BETWEEN gi_begindate AND gi_enddate");
		sql.append(" AND g.gi_state=0");//上架
		sql.append(" AND gi_shop_code = :shop_code");
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Shop_GiftList.class));
	}
	/**
	 * 1.根据ID查询礼品是否数量充足
	 * 2.若充足则扣除数量
	 * 3.保存领取记录
	 * */
	@Override
	public void send(Map<String, Object> param) {
		String vm_code = StringUtil.trimString(param.get("vm_code"));
		String shop_code = StringUtil.trimString(param.get(CommonUtil.SHOP_CODE));
		String companyid = StringUtil.trimString(param.get(CommonUtil.COMPANYID));
		String em_code =StringUtil.trimString(param.get(CommonUtil.EMP_CODE));
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT gil_id,gil_totalamount-gil_getamount gil_amount,gil_pd_code,");
		sql.append(" gil_cr_code,gil_sz_code,gil_bs_code,");
		sql.append( "'"+shop_code+"' gil_shop_code,'"+vm_code+"' gil_vm_code,");
		sql.append( companyid+" companyid,'"+em_code+"' gil_user_code,");
		sql.append( "'"+DateUtil.getYearMonthDate()+"' gil_sysdate");
		sql.append(" FROM t_shop_giftlist t");
		sql.append(" WHERE gil_id in(:ids)");
		List<T_Shop_GiftList> list = namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Shop_GiftList.class));
		if(check(list, param)){
			//更新礼品表的数量
			sql.setLength(0);
			sql.append(" update t_shop_giftlist");
			sql.append(" SET gil_getamount=gil_getamount+:gil_amount");
			sql.append(" WHERE gil_id=:gil_id");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), 
					SqlParameterSourceUtils.createBatch(list.toArray()));
			//保存领取明细
			sql.setLength(0);
			sql.append("INSERT INTO t_shop_gift_send(");
			sql.append("gs_pd_code,gs_cr_code,gs_sz_code,gs_br_code,gs_amount,");
			sql.append("gs_last_amount,gs_state,gs_shop_code,gs_em_code,gs_vm_code,");
			sql.append("gs_send_date,gs_date,gs_sysdate,gs_type,companyid");
			sql.append(")VALUES(");
			sql.append(":gil_pd_code,:gil_cr_code,:gil_sz_code,:gil_bs_code,:gil_amount,");
			sql.append(":gil_amount,1,:gil_shop_code,:gil_user_code,:gil_vm_code,");
			sql.append(":gil_sysdate,:gil_sysdate,sysdate(),0,:companyid");
			sql.append(")");
			namedParameterJdbcTemplate.batchUpdate(sql.toString(), 
					SqlParameterSourceUtils.createBatch(list.toArray()));
		}
	}
	@SuppressWarnings("unchecked")
	public boolean check(List<T_Shop_GiftList> list,Map<String, Object> param){
		boolean flag = true;
		if(null != list && list.size() > 0){
			List<T_Shop_GiftList> _list = (List<T_Shop_GiftList>)param.get("list");
			for(T_Shop_GiftList gift:list){
				for(T_Shop_GiftList _gift:_list){
					if(_gift.getGil_id() == gift.getGil_id()){
						if(gift.getGil_amount() < _gift.getGil_amount()){
							flag = false;
							break;
						}
					}
				}
				if(!flag){
					break;
				}
			}
			for(T_Shop_GiftList gift:list){
				for(T_Shop_GiftList _gift:_list){
					if(_gift.getGil_id() == gift.getGil_id()){
						gift.setGil_amount(_gift.getGil_amount());
					}
				}
			}
		}
		return flag;
	}

	@Override
	public Integer report_count(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object gs_shop_code = param.get("gs_shop_code");
		Object begindate = param.get("begindate");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT count(1)");
		sql.append(" FROM t_shop_gift_send t");
		sql.append(" JOIN t_base_shop sp ON gs_shop_code = sp_code AND t.companyid = sp.companyid");
		if(StringUtil.isNotEmpty(gs_shop_code)){
			sql.append(" AND gs_shop_code=:gs_shop_code");
		}
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.gs_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		return namedParameterJdbcTemplate.queryForObject(sql.toString(), param ,Integer.class);
	}

	@Override
	public List<T_Shop_Gift_Send> report_list(Map<String, Object> param) {
		Object shop_type = param.get(CommonUtil.SHOP_TYPE);
		Object begindate = param.get("begindate");
		Object gs_shop_code = param.get("gs_shop_code");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT gs_pd_code,gs_cr_code,gs_sz_code,gs_br_code,");
		sql.append(" pd_no,pd_name,sp_name shop_name,vm_name,");
		sql.append(" (SELECT cr_name FROM t_base_color cr WHERE cr_code = gs_cr_code AND cr.companyid = t.companyid LIMIT 1) AS cr_name,");
		sql.append(" (SELECT sz_name FROM t_base_size sz WHERE sz_code = gs_sz_code AND sz.companyid = t.companyid LIMIT 1) AS sz_name,");
		sql.append(" (SELECT br_name FROM t_base_bra br WHERE br_code = gs_br_code AND br.companyid = t.companyid LIMIT 1) AS br_name,");
		sql.append(" gs_amount,gs_last_amount,gs_state,gs_shop_code,");
		sql.append(" gs_em_code,gs_vm_code,gs_send_date,gs_date,");
		sql.append(" gs_type");
		sql.append(" FROM t_shop_gift_send t");
		sql.append(" JOIN t_vip_member v");
		sql.append(" ON v.vm_code=t.gs_vm_code");
		sql.append(" AND v.companyid=t.companyid");
		sql.append(" JOIN t_base_product p");
		sql.append(" ON p.pd_code=gs_pd_code");
		sql.append(" AND p.companyid=t.companyid");
		sql.append(" JOIN t_base_shop sp ON gs_shop_code = sp_code AND t.companyid = sp.companyid");
		if(StringUtil.isNotEmpty(gs_shop_code)){
			sql.append(" AND gs_shop_code=:gs_shop_code");
		}
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 1));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND t.gs_date BETWEEN :begindate AND :enddate");
		}
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY gs_id DESC");
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), param, 
				new BeanPropertyRowMapper<>(T_Shop_Gift_Send.class));
	}
	
}
